<?php

function formula_umum( $str , $karyawan_id , $konstan ){
//	var_dump($konstan);
	$periode_id =get_periode_gaji();

	$formulas = array();
	$formulas['[HARI_BULAN_BERIKUT]'] =$konstan['HARI_BULAN_BERIKUT'];
	$formulas['[HARI_BULAN]'] = $konstan['HARI_BULAN'];
	$formulas['[HARI_BULAN_SEBELUM]'] = $konstan['HARI_BULAN_SEBELUM'];
	$formulas['[HARI_BULAN_SEBELUM_11]'] = $konstan['HARI_BULAN_SEBELUM_11'];
	$formulas['[HARI_NORMAL_11]'] = $konstan['HARI_NORMAL_11'];

	$formulas['[GAPOK]'] = 'formula_get_gapok($karyawan_id)';
	$formulas['[MASA_KERJA_TAHUN]'] = 'formula_get_masa_kerja($karyawan_id)';
	$formulas['[TANGGUNGAN_KELUARGA]'] = 'formula_get_marital_status($karyawan_id)';
	$formulas['[HUT_TMB]'] = 'formula_get_hut_tmb($karyawan_id)';
	$formulas['[INSENTIF]'] = 'formula_get_insentif($karyawan_id)';
	$formulas['[PREMI_KESEHATAN]'] = 'formula_get_premi_kesehatan($karyawan_id)';
	$formulas['[MEDICAL_CLAIM]'] = 'medical_claim($karyawan_id , '.$periode_id.')';
	$formulas['[STATUS_PREMI]'] = 'formula_get_status_premi($karyawan_id)';

	$query  = "SELECT * FROM a_absen_payroll WHERE karyawan_id = {$karyawan_id}  
 	AND periode_id={$periode_id}	";
	$res = my_query($query);
	$rw = my_fetch_array($res);
	$formulas['[HARI_KERJA_11]'] = trim($rw['hari_kerja_11_10'])==''?0:$rw['hari_kerja_11_10'];
	$formulas['[LEMBUR_OTOMATIS]'] = trim($rw['lembur_otomatis'])==''?0:$rw['lembur_otomatis'];
	$formulas['[JUMLAH_JAM]'] = trim($rw['lembur_jumlah_jam'])==''?0:$rw['lembur_jumlah_jam'];
	$formulas['[CITO]'] = trim($rw['cito_11_10'])==''?0:$rw['cito_11_10'];
	$formulas['[LEMBUR]'] = trim($rw['hourpay'])==''?0:$rw['hourpay'];
	
	/*GUNTUR ADD KOMPONEN GAJI*/
	$formulas['[KJK]'] = 'formula_get_payroll_kjk($karyawan_id)';
	$formulas['[ABSENSI]'] = 'formula_get_payroll_absensi($karyawan_id)';
	$formulas['[OVERTIME_OTOMATIS]'] = 'formula_get_payroll_overtime_otomatis($karyawan_id)';
	$formulas['[OVERTIME]'] = 'formula_get_payroll_overtime($karyawan_id)';
	$formulas['[OVERTIME56]'] = 'formula_get_payroll_overtime_more_56($karyawan_id)';
	$formulas['[TAMB_UANG_MAKAN]'] = 'formula_get_payroll_tunjangan_tambahan_uang_makan($karyawan_id)'; 
	$formulas['[POT_UANG_MAKAN]'] = 'formula_get_payroll_potongan_uang_makan($karyawan_id)'; 
	$formulas['[LEMBUR_LEBIH4JAM]'] = 'formula_hitung_overtime_dapat_uang_makan($karyawan_id)';
	$formulas['[PERUMAHAN]'] = 'get_data_tunjangan_perumahan($karyawan_id)';
	$formulas['[POT_TRANSPORT]'] = 'get_potongan_transport($karyawan_id ,$tanggal,$komponen_gaji_id)';
	
	
	$query  = "SELECT * FROM a_rekomendasi_lembur WHERE karyawan_id = {$karyawan_id}  
 	AND periode_id={$periode_id}	";
	$res = my_query($query);
	if(my_num_rows($res)>0){
		$temp=$rw['hourpay_56']-$rw['hourpay'] ;
	}else{
		$temp='' ;
	}
	$formulas['[LEMBUR_56]'] = trim($temp)==''?0:$temp ;
	$formulas['[POTONG_ABSEN_JAM]'] = trim($rw['potong_absen_jam'])==''?0:$rw['potong_absen_jam'];
	$formulas['[NOMINAL_TRANSPORT]']=get_tunjangan_transport_by_karyawan_id($karyawan_id) ;
	$formulas['[POTONG_TRANSPORT]'] =trim($rw['potong_transport'])==''?0:$rw['potong_transport'] ;

	$text = $str;
	foreach($formulas as $key => $val ){ 
		$text  = str_replace($key , $val , $text); 
	}
	return $text ;
}

//ADD GUNTUR
function get_total_hadir_sebelas_31_next($karyawan_id){
	$query = "SELECT DISTINCT  tgl_index FROM temp_tmn_hari  WHERE 
		karyawan_id = {$karyawan_id} AND
		tgl_index > 10 AND (
		status_absen = 'N' OR
		status_absen = '1' OR
		status_absen = '2' OR
		status_absen = '3' OR
		status_absen = '4' OR
		status_absen = '5' OR
		status_absen = '6' OR
		status_absen = '7' OR
		status_absen = '8' OR
		status_absen = '9'  
		)";
	$result = my_query($query); 
	$rrecord = my_num_rows($result);
	return $rrecord;
}

function get_data_tunjangan_perumahan( $karyawan_id ){
	$query = "SELECT nominal FROM pr_perumahan WHERE karyawan_id = {$karyawan_id} ORDER BY fas_rumah_id DESC LIMIT 1";
	$result = my_query($query);
	if( my_num_rows($result) > 0){
		$row = my_fetch_array($result);
		return (string)$row['nominal'];
	}
	return false;
}

function get_total_lembur_up_4jam_sebelas_31_next($karyawan_id){
	$q = "SELECT MONTH(tanggal) AS bln , YEAR(tanggal) AS thn FROM temp_tmn_overtime ORDER BY tanggal ASC LIMIT 1";
	$r = my_query($q);
	$tr = my_fetch_array($r);
	$query = "SELECT COUNT(*) AS ttl FROM temp_tmn_overtime  WHERE 
		karyawan_id = {$karyawan_id}  
		AND DATE(tanggal) > '{$tr['thn']}-{$tr['bln']}-10'
		AND CAST(`hour` AS DECIMAL(2,0))  >= 4 "; 	

	$result = my_query($query);
	$row = my_fetch_array($result);
	return $row['ttl'];
}


function get_total_lembur_satu_10_prev($karyawan_id){
	$q = "SELECT MONTH(tanggal) AS bln , tanggal FROM tmn_overtime ORDER BY tanggal DESC LIMIT 1";
	$r = my_query($q);
	$tr = my_fetch_array($r);
	list($thn ,$bulan , $day) = explode("-" ,$tr['tanggal'] );
	$bln = (int) $tr['bln'];
	if($bln == 1){
		$date_start = ($thn-1) ."-12-01";
		$date_end = ($thn-1) ."-12-10";
	}else{
		$bulan = $bln - 1;
		$date_start = $thn  ."-".sprintf('%02d',$bulan)."-01";
		$date_end = $thn ."-".sprintf('%02d',$bulan) ."-10";
	}
	$query = "SELECT COUNT(*) AS ttl FROM  tmn_overtime  WHERE 
		karyawan_id = {$karyawan_id}  
		AND ( DATE(tanggal) BETWEEN '{$date_start}' AND '{$date_end}' )
		AND CAST(`hour` AS DECIMAL(2,0))  >= 4 "; 
	
	$result = my_query($query);
	$row = my_fetch_array($result);
	return $row['ttl'];
	
}
 
function get_total_hadir_satu_10_prev($karyawan_id){
	$cmax = "SELECT MAX(periode_id) as max_id FROM tmn_hari_kerja";
	$rmax = my_query($cmax);
	$rowmax = my_fetch_array($rmax);
	$max_periode_id = $rowmax['max_id'];
	
	$query = "	SELECT 
					  save_hari_prev_um   
				FROM tmn_hari_kerja
				WHERE karyawan_id = {$karyawan_id}
				AND periode_id = {$max_periode_id}
				AND  save_hari_prev_um <> ''
				ORDER BY tmn_hari_kerja_id 
				DESC LIMIT 1";
	$result = my_query($query);
	$row = my_fetch_array($result);
	return $row['save_hari_prev_um'];
}

function hari_konsumsi_makan($karyawan_id){
	$abc = get_total_hadir_satu_10_prev($karyawan_id); 
	$def = get_total_lembur_satu_10_prev($karyawan_id); 
	$ghi = get_total_lembur_up_4jam_sebelas_31_next($karyawan_id);
	$jkl = get_total_hadir_sebelas_31_next($karyawan_id);
	
	$datas = array(
		'karyawan_id'	=>my_type_data_int($karyawan_id),
		'satu_sepuluh'	=>my_type_data_int($abc),
		'lembur_satu_sepuluh'	=>my_type_data_int($def),
		'lembur_31'	=>my_type_data_int($ghi),
		'masuk_31'	=>my_type_data_int($jkl),
	);
	my_insert_record('x_check_hari_konsumsi' , $datas);
	
	return $abc + $def  + $ghi + $jkl;
}
 

function formula_get_payroll_potongan_uang_makan($karyawan_id){

	$total_konsumsi = hari_konsumsi_makan($karyawan_id); 
	$hari_konsumsi = formula_get_hari_bulan_konsumsi();
	if($total_konsumsi < $hari_konsumsi ){
		return   ($hari_konsumsi- $total_konsumsi)  ;
	}
}

function formula_get_payroll_tunjangan_tambahan_uang_makan($karyawan_id){
 
	$total_konsumsi = hari_konsumsi_makan($karyawan_id);
	$hari_konsumsi = formula_get_hari_bulan_konsumsi();
	if($total_konsumsi > $hari_konsumsi ){
		return   ( $total_konsumsi - $hari_konsumsi	) ;
	}	
}

function formula_get_payroll_overtime_more_56($karyawan_id){
	$query  = "SELECT   SUM(nominal) as tunj FROM temp_tmn_overtime WHERE karyawan_id = {$karyawan_id} AND out_of_count = 'Y' ";
	$res = my_query($query);
	$rw = my_fetch_array($res); 
	return round($rw['tunj']) ;
}

function formula_get_payroll_overtime($karyawan_id){
	$query  = "SELECT   SUM(nominal) as tunj FROM temp_tmn_overtime WHERE karyawan_id = {$karyawan_id} AND out_of_count = 'N' ";
	$res = my_query($query);
	$rw = my_fetch_array($res); 
	return round($rw['tunj']) ;
}

function formula_get_payroll_overtime_otomatis($karyawan_id){
	$query  = "SELECT   nominal FROM temp_tmn_khk WHERE karyawan_id = {$karyawan_id} ";
	$res = my_query($query);
	$rw = my_fetch_array($res); 
	return $rw['nominal'] ;
}

function formula_get_payroll_absensi($karyawan_id){
 
	$query  = "SELECT   nominal FROM temp_tmn_absensi WHERE karyawan_id = {$karyawan_id} ";
	$res = my_query($query);
	$rw = my_fetch_array($res);
	
	return $rw['nominal'] ;
}	

function formula_get_payroll_kjk($karyawan_id){
 
	$query  = "SELECT   nominal FROM temp_tmn_kjk WHERE karyawan_id = {$karyawan_id} ";
	$res = my_query($query);
	$rw = my_fetch_array($res);
	
	return $rw['nominal'] ;
}	

//END ADD GUNTUR



function formula_get_gapok($karyawan_id){
 
	$query  = "SELECT gapok_km FROM kekaryawanan WHERE karyawan_id = {$karyawan_id} ";
	$res = my_query($query);
	$rw = my_fetch_array($res);
	
	return $rw['gapok_km'] ;
}	

function formula_get_hari_normal($hari_aktual){
	$periode_id =get_periode_gaji();
	$periode = my_get_data_by_id('pr_periode','periode_id',$periode_id);
	$bulan = (int) date('m' , strtotime($periode['tanggal']));
	$tahun = $periode['tahun'] ;
	
	$enddate=$tahun.'-'.sprintf( '%02d',$bulan) .'-10';
	if($bulan =='01' ){
		$bulan = '12';
		$tahun -- ;	
	}else{
		$bulan-- ;	
	}		
	$startdate=$tahun.'-'.sprintf( '%02d',$bulan) .'-11';
	
	$res=jumlah_hari_not_weekend($startdate , $enddate  );
	$res-=count_hari_libur($startdate , $enddate  )	;
	return $res;
}	


function formula_get_marital_status($karyawan_id){
 
	$query  = "SELECT marital FROM kekaryawanan WHERE karyawan_id = {$karyawan_id} ";
	$res = my_query($query);
	$rw = my_fetch_array($res);
	
	if($rw['marital'] =='K')return 1;
	
	return 0;
}	

function formula_get_masa_kerja($karyawan_id){
	$query = "SELECT tmb FROM kekaryawanan WHERE karyawan_id={$karyawan_id}";
	$res = my_query($query);
	$rw = my_fetch_array($res);	
	return getage($tr['tmb']);
}	

function formula_get_cito($karyawan_id){
	$dates=get_startdate_enddate();
	$enddate=$dates['enddate'];
	$startdate=$dates['startdate'];

	$query = "SELECT dua_jam,empat_jam from woc_tarif a 
		INNER JOIN kekaryawanan b ON a.golongan_jabatan_id=b.golongan_jabatan_id 
		WHERE b.karyawan_id = {$karyawan_id }";	
	$tarifs = my_fetch_array(my_query($query));

	$query = "SELECT jumlah_jam FROM work_on_call_detail a 
		INNER JOIN work_on_call b ON a.work_on_call_id=b.work_on_call_id 	
		WHERE a.karyawan_id={$karyawan_id} 
		AND b.tanggal BETWEEN '".$startdate."' AND '".$enddate."'" ;
	$res = my_query($query);
	$nominal_cito=0;
	while($ey = my_fetch_array($res)){
		if( (int) $ey['jumlah_jam']  < 4){
			$nominal_cito+=$ey['jumlah_jam']*$tarifs['dua_jam'] ;
		}else{
			$nominal_cito+=$ey['jumlah_jam']*$tarifs['empat_jam'] ;
		}
	}
	return $nominal_cito;
}	

function formula_get_hut_tmb($karyawan_id){
	//nilai return 4=cuti besar, 1=cuti tahunan, 0=else
	$periode_id =get_periode_gaji();
	$periode = my_get_data_by_id('pr_periode','periode_id',$periode_id);
	$bulan = (int) date('m' , strtotime($periode['tanggal']));
	$tahun = (int) date('Y' , strtotime($periode['tanggal']));
	
	$query  = "SELECT tmb FROM kekaryawanan WHERE karyawan_id = {$karyawan_id} ";
	$rw = my_fetch_array(my_query($query));
	$tmb=$rw['tmb'] ;
	$bulan_tmb = (int) date('m' , strtotime($tmb));
	$tahun_tmb = (int) date('Y' , strtotime($tmb));
	if($bulan==$bulan_tmb){
		if($tahun==$tahun_tmb){
			return 1;
		}
		$masatahun= $tahun-$tahun_tmb;
		if($tahun_tmb < 2000){
			$temp= (2000-$tahun_tmb)%3 ;
			if($temp==0){
				$temp=3;
			}
			$temp=($tahun-(2000-$temp))/4;
			$temp= $temp  - (int) $temp ;
			if($temp==0){
				return 4;
			}
			return 1;
		}else{
			$temp=($masatahun/4);
			$temp=$temp - (int) $temp ;
			if($temp==0){
				return 4;
			}   
			return 1;
		}
	}	
	return 0;
}	

function formula_get_insentif($karyawan_id){
 	$periode_id =get_periode_gaji();
	$periode = my_get_data_by_id('pr_periode','periode_id',$periode_id);
	$bulan = (int) date('m' , strtotime($periode['tanggal']));
	$tahun = (int) date('Y' , strtotime($periode['tanggal']));
	$query  = "SELECT * FROM pr_insentif_karyawan a
		INNER JOIN pr_insentif b ON a.insentif_id = b.insentif_id 
		WHERE a.karyawan_id = {$karyawan_id} 
		AND MONTH(b.tanggal)='".$bulan."'
		AND YEAR(b.tanggal)='".$tahun."'"
	;	
		
	$rw = my_fetch_array(my_query($query));
	return $rw['nominal'] ;
}	

function get_status_direktur_by_karyawan_id($karyawan_id){
	$query= "SELECT  nik  FROM karyawan 
		WHERE  karyawan_id = {$karyawan_id}	";
	$result = my_query($query);
	$row = my_fetch_array($result);
	$fid = (int) substr($row['nik'] ,0,1);
	if($fid == 3){
		return true;
	}
	return false;
}

function formula_get_premi_kesehatan($karyawan_id ){
	
	// return 0=ada by kes (tidak dapat premi), 1=medical single, 2=medical married
 	$periode_id =get_periode_gaji();
	$last_periode_id=get_last_periode_id($periode_id) ;
	$periode = my_get_data_by_id('pr_periode','periode_id',$last_periode_id);
	$bulan = (int) date('m' , strtotime($periode['tanggal']));
	$tahun = (int) date('Y' , strtotime($periode['tanggal']));
	/*$query  = "SELECT * FROM kes_biaya_kesehatan 
		WHERE karyawan_id = {$karyawan_id} 
		AND MONTH(tanggal)='".$bulan."'
		AND YEAR(tanggal)='".$tahun."'"
	;*/
	$query  = "SELECT * FROM pr_upload_pengobatan 
		WHERE karyawan_id = {$karyawan_id}  AND periode_id = {$periode_id}" 
	;	
	$result=my_query($query) ;
	
	$tdata = my_num_rows($result);
	if( $tdata > 0 ){	 
		return 0 ;
	}else{	
		$query  = "SELECT premi_kesehatan FROM kekaryawanan
			WHERE karyawan_id = {$karyawan_id}"
		;
		 
		$ted= my_query($query);
		$rw = my_fetch_array($ted); 
		if($rw['premi_kesehatan']=='D'){
			return 2;
		}else{
			return 1;
		}	
	}
	return 0 ;
}	 

function formula_get_karyawan_cerai($karyawan_id){
	$karyawan = loaddata_karyawan($karyawan_id);
	$data_true = array('duda','janda');
	if( in_array( $karyawan['marital'] , $data_true) ){
		return 1;
	}
	return 0; 
}

function formula_get_cuti_besar($karyawan_id){
	$karyawan = loaddata_karyawan($karyawan_id);
	$data_true = array('duda','janda');
	if( in_array( $karyawan['marital'] , $data_true) ){
		return 1;
	}
	return 0; 
}

/*

([FASKES]==1 ? 0 :	([MARITAL]==2 ? 0 : MIN(0.15*[GAPOK],200000)));
([FASKES]==1 ? 0 :	([MARITAL]==2 ? MIN(0.15*[GAPOK],200000) : 0));
if([FASKES]=1,0,if([MARITAL]=2,MIN(0.15*[GAPOK],200000,0)))
IF(([REALISASI_HARI]+[UML_4_JAM])*15000-[UPMK_BL]<0,([REALISASI_HARI]+[UML_4_JAM])*15000-[UPMK_BL],0)
IF(([REALISASI_HARI]+[UML_4_JAM])*15000-[UPMK_BL]>0,([REALISASI_HARI]+[UML_4_JAM])*15000-[UPMK_BL],0)

if([HUT_TMB]=1 AND mod([MASA_KERJA_TAHUN],4)=0,[GAPOK]*2,0)
([HUT_TMB]==1 ? [GAPOK] : 0)
(([POTONGAN_HARI]*8/173)+([POTONGAN_JAM]/173)+([POTONGAN_MENIT]/(60*173)))*[GAPOK]*0.5

a_realisasi_log
	periode_id

*/
function get_startdate_enddate(){
	$res=array();
	$periode_id =get_periode_gaji();
	$periode = my_get_data_by_id('pr_periode','periode_id',$periode_id);
	$bulan = (int) date('m' , strtotime($periode['tanggal']));
	$tahun = $periode['tahun'] ;
	$res['enddate']=$tahun.'-'.sprintf( '%02d',$bulan) .'-10';
	if($bulan =='01' ){
		$bulan = '12';
		$tahun-- ;	
	}else{
		$bulan-- ;
	}		
	$res['startdate']=$tahun.'-'.sprintf( '%02d',$bulan) .'-11';
	return $res ;
}	


function get_tunjangan_transport_by_karyawan_id($karyawan_id){
	$periode_id =get_periode_gaji();
	$periode = my_get_data_by_id('pr_periode','periode_id',$periode_id);
	$tanggal = $periode['tanggal'];	
	$query = "
		SELECT sk_id FROM pr_sk_tunjangan 
		WHERE komponen_gaji_id =5  
		AND tanggal<='".$tanggal."' 
		ORDER BY tanggal DESC LIMIT 1
		";
		
		//5=Transport
	$rw = my_fetch_array(my_query($query));
	$sk_id= isset( $rw['sk_id'] ) ? $rw['sk_id']:  0;
	
	$query = "
		SELECT nominal FROM pr_sk_tunjangan_detail a
		INNER JOIN kekaryawanan b ON b.golongan_jabatan_id = a.sub_id 
		WHERE b.karyawan_id = {$karyawan_id } 
		AND a.komponen_gaji_id=5 
		AND a.sk_id={$sk_id}
	";	
	$dres = my_query($query);
	if( my_num_rows($dres) == 0 ){	
		return 0 ;
	}
	$drow = my_fetch_array($dres);
	$hasil_type_entry = (int) $drow['nominal'];
	return 	$hasil_type_entry ;
}	


function medical_should_be( $karyawan_id , $max_kenaikan ){
	
	//CHECK MARITAL
	$check_karyawan_status = "SELECT premi_kesehatan,gapok_km FROM pr_gaji 
	WHERE karyawan_id = {$karyawan_id} AND periode_id = 0 ";
	$result = my_query($check_karyawan_status);
	$rowck = my_fetch_array($result);
	
	if($rowck['premi_kesehatan'] == 'D'){
		$nm = round(0.15 * $rowck['gapok_km']);
		if($nm < 200000)return 200000;
		else return $nm;
	}
	$nm = round(0.075 * $rowck['gapok_km']);
	if($nm < 150000)return 150000;
	else return $nm;
}

function medical_claim($karyawan_id , $periode_id){
	
	$query = "SELECT nominal FROM pr_upload_pengobatan 
	WHERE karyawan_id = {$karyawan_id} AND periode_id = {$periode_id} ";
//	if((int) $karyawan_id == 219){
	//print($query);exit}
	$result = my_query($query);
	if(my_num_rows($result) > 0){
		$row = my_fetch_array($result);
		return $row['nominal'];
	}
	return false;
}